package com.example.transaction;

import com.example.bean.User;
import com.example.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class TransactionTest {

    // 通用的增删改操作
    public int update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 编译SQL
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 执行操作
            return preparedStatement.executeUpdate();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
        return 0;
    }

    /**
     * 针对数据表user_table，实现AA用户向BB用户转账100
     * 转账成功场景
     */
    @Test
    public void testUpdate() {
        String sql1 = "UPDATE user_table SET balance = balance - 100 WHERE user = ?";
        int result1 = update(sql1, "AA");
        if (result1 > 0) {
            System.out.println("AA转出成功");
        }

        String sql2 = "UPDATE user_table SET balance = balance + 100 WHERE user = ?";
        int result2 = update(sql2, "BB");
        if (result2 > 0) {
            System.out.println("BB转入成功");
        }

        System.out.println("转账成功");
    }

    /**
     * 针对数据表user_table，实现AA用户向BB用户转账100
     * 转账失败场景，出现数据不一致的情况
     */
    @Test
    public void testUpdate1() {
        String sql1 = "UPDATE user_table SET balance = balance - 100 WHERE user = ?";
        int result1 = update(sql1, "AA");
        if (result1 > 0) {
            System.out.println("AA转出成功");
        }

        // 模拟网络异常
        System.out.println(1 / 0);

        String sql2 = "UPDATE user_table SET balance = balance + 100 WHERE user = ?";
        int result2 = update(sql2, "BB");
        if (result2 > 0) {
            System.out.println("BB转入成功");
        }

        System.out.println("转账成功");
    }

    // 通用的增删改操作（考虑事务）
    public int update(Connection connection, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        try {
            // 编译SQL
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 执行操作
            return preparedStatement.executeUpdate();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(null, preparedStatement);
        }
        return 0;
    }

    /**
     * 针对数据表user_table，实现AA用户向BB用户转账100
     * 考虑事务的情况下
     */
    @Test
    public void testUpdateWithTx() {
        Connection connection = null;
        boolean autoCommit = false;
        try {
            connection = JDBCUtils.getConnection();
            autoCommit = connection.getAutoCommit();
            System.out.println("自动提交:" + autoCommit);
            // 取消自动提交
            connection.setAutoCommit(false);

            String sql1 = "UPDATE user_table SET balance = balance - 100 WHERE user = ?";
            int result1 = update(connection, sql1, "AA");
            if (result1 > 0) {
                System.out.println("AA转出成功");
            }

            // 模拟网络异常
            System.out.println(1 / 0);

            String sql2 = "UPDATE user_table SET balance = balance + 100 WHERE user = ?";
            int result2 = update(connection, sql2, "BB");
            if (result2 > 0) {
                System.out.println("BB转入成功");
            }

            System.out.println("转账成功");
            // 提交操作
            connection.commit();
        } catch (Exception exception) {
            // 回滚操作
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            exception.printStackTrace();
        } finally {
            // 恢复其以前的自动提交状态
            if (connection != null) {
                try {
                    connection.setAutoCommit(autoCommit);
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 通用查询单条记录（考虑事务）
    public <T> T getInstance(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 编译SQL
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            // 查询结果
            resultSet = preparedStatement.executeQuery();
            // 获取结果集元数据
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();

            // 遍历结果集
            if (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    // 获取列名
                    String columnName = resultSetMetaData.getColumnLabel(i + 1);

                    // 通过反射设置属性值
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(null, preparedStatement, resultSet);
        }
        return null;
    }

    @Test
    public void testTransactionSelect() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            // 获取当前连接的隔离级别 Connection类中常量
            // int TRANSACTION_NONE = 0;
            // int TRANSACTION_READ_UNCOMMITTED = 1;
            // int TRANSACTION_READ_COMMITTED = 2;
            // int TRANSACTION_REPEATABLE_READ = 4;
            // int TRANSACTION_SERIALIZABLE = 8;
            int transactionIsolation = connection.getTransactionIsolation();
            System.out.println(transactionIsolation);
            // 设置隔离级别
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

            // 取消自动提交
            connection.setAutoCommit(false);

            String sql = "select user, password, balance from user_table where user = ?";
            User user = getInstance(connection, User.class, sql, "AA");
            System.out.println(user);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    @Test
    public void testTransactionUpdate() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            // 取消自动提交
            connection.setAutoCommit(false);
            String sql = "update user_table set balance = ? where user = ?";
            update(connection, sql, 6000, "AA");

            Thread.sleep(15000);
            System.out.println("修改结束");
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

}
